﻿/**
 *       Project: ServerLib
 *     Copyright: Copyright (C) 2008, Rob Kennedy
 *   Create Date: 2008-07-14 10:16 PM
 * Modified Date: 2008-07-14 10:16 PM    RK  Added comments
 *      Comments: Ignore SA1633.
 *      
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

namespace ServerLib.Data
{
    using System;
    using System.Collections;
    using ServerLib.Core;

    /// --------------------------------------------------------------------
    /// Project	 : ServerLib
    /// Class	 : SqlCommandBuilder
    /// Inherits : None.
    /// --------------------------------------------------------------------
    /// <summary>
    /// Builds a database SQL command string from individual property values
    /// </summary>
    /// <authorname>Rob Kennedy</authorname>
    /// <remarks></remarks>
    /// --------------------------------------------------------------------
    public class SqlCommandStringBuilder
    {
        #region Public Enumerations
        
        /// <summary>
        ///  Contains an enumerated list of standard SQL command verbs
        /// </summary>
        public enum SqlStringCommandType
        {
            /// <summary>SELECT Command</summary>
            Select = 0, 

            /// <summary>INSERT Command</summary>
            Insert,

            /// <summary>UPDATE Command</summary>
            Update,

            /// <summary>DELETE Command</summary>
            Delete,

            /// <summary>EXECUTE Command</summary>
            Execute
        }
        
        /// <summary>
        ///  Contains an enumerated list of standard SQL sort order options
        /// </summary>
        public enum SelectOrderDirection
        {
            /// <summary>No sorting</summary>
            None = 0,

            /// <summary>Sort in ascending order</summary>
            Ascending,

            /// <summary>Sort in descending order</summary>
            Descending
        }

        #endregion

        #region Public Methods
        /// <summary>
        ///  Converts single-quote "'" characters within a string to 
        ///  Double single-quote "''" characters
        /// </summary>
        /// <param name="stringValue">The string to check for quote encoding</param>
        /// <returns>Returns a string with single-quotes encoded</returns>
        public static string EncodeSingleQuotes(string stringValue)
        {
            return stringValue.Replace("'", "''");
        }

        /// <summary>
        ///  Inserts parameter values into a tokenized SQL string. String values are
        ///  single-quote encoded, and null values are converted to "NULL".
        /// </summary>
        /// <param name="sqlCommand">The tokenized SQL string to insert values into.</param>
        /// <param name="paramValues">Optional parameter values to insert into the SQL string</param>
        /// <returns>Returns the passed inSQL string with values located </returns>
        public static string InjectParameterValues(string sqlCommand, params object[] paramValues)
        {
            string resultValue = sqlCommand;
            if (paramValues != null ? paramValues.Length > 0 : false)
            {
                for (int i = 0; i < paramValues.Length; i++)
                {
                    if (paramValues[i] != DBNull.Value && paramValues[i] != null)
                    {
                        if (Convert.ToString(paramValues[i]).IndexOf("'") > 0)
                        {
                            paramValues[i] = EncodeSingleQuotes(Convert.ToString(paramValues[i]));
                        }
                        else if (paramValues[i] is bool)
                        {
                            if (Types.FixBoolean(paramValues[i]))
                            {
                                paramValues[i] = 1;
                            }
                            else
                            {
                                paramValues[i] = 0;
                            }
                        }
                    }
                    else
                    {
                        paramValues[i] = "NULL";
                    }
                }

                resultValue = string.Format(sqlCommand, paramValues);
            }

            // clean-up any oddities like quoted nulls.
            resultValue = resultValue.Replace("'NULL'", "NULL");
            
            return resultValue;
        }

        /// <summary>
        ///  Builds a SQL string based on the parameters passed
        /// </summary>
        /// <param name="commandType">The SQL command verb to use</param>
        /// <param name="tableName">The database table name to execute against</param>
        /// <param name="whereClause">SQL where-clause filter</param>
        /// <param name="fieldNames">field names to execute against</param>
        /// <param name="fieldValues">Optional parameters are values to excute with assiciated field names</param>
        /// <returns>Returns a complete SQL string</returns>
        public static string BuildSqlCommand(SqlStringCommandType commandType, string tableName, string whereClause, string[] fieldNames, object[] fieldValues)
        {
            string resultValue = string.Empty;
            switch (commandType)
            {
                case SqlStringCommandType.Select:
                    resultValue = string.Format("SELECT {0} FROM {1}", (fieldNames != null ? ArrayToSQLString(fieldNames) : "*"), tableName);
                    break;
                case SqlStringCommandType.Insert:
                    resultValue = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tableName, ArrayToSQLString(fieldNames), ArrayToSQLValues(fieldValues));
                    break;
                case SqlStringCommandType.Update:
                    resultValue = string.Format("UPDATE {0} SET {1}", tableName, ArrayToUpdateSQLValues(fieldNames, fieldValues));
                    break;
                case SqlStringCommandType.Delete:
                    resultValue = string.Format("DELETE FROM {0}", tableName);
                    break;
                case SqlStringCommandType.Execute:
                    resultValue = string.Format("EXEC {0} {1}", tableName, ArrayToSQLValues(fieldValues));
                    break;
            }

            if (whereClause != null ? whereClause.Trim().Length > 0 : false)
            {
                resultValue += string.Format(" WHERE {0}", whereClause);
            }

            return resultValue;
        }

        /// <summary>
        ///  Shorthand command to build a SQL string that will INSERT values into a table
        /// </summary>
        /// <param name="tableName">The table to insert values into</param>
        /// <param name="fieldNames">The field names to insert values into</param>
        /// <param name="fieldValues">parameter values to insert into the table</param>
        /// <returns>Returns an INSERT SQL command string</returns>
        public static string BuildInsert(string tableName, string[] fieldNames, object[] fieldValues)
        {
            return BuildSqlCommand(SqlStringCommandType.Insert, string.Empty, string.Empty, fieldNames, fieldValues);
        }

        /// <summary>
        /// Shorthand command to build a SQL string that will SELECT values from a table
        /// </summary>
        /// <param name="tableName">The table to retrieve values from</param>
        /// <param name="fieldNames">The field names to retrieve</param>
        /// <returns>Returns a SELECT SQL command string</returns>
        public static string BuildSelect(string tableName, string[] fieldNames)
        {
            return BuildSqlCommand(SqlStringCommandType.Select, tableName, string.Empty, fieldNames, null);
        }

        /// <summary>
        /// Shorthand command to build a SQL string that will SELECT values from a table
        /// </summary>
        /// <param name="tableName">The table to retrieve values from</param>
        /// <param name="fieldNames">The field names to retrieve</param>
        /// <param name="whereClause">SQL where-clause filter</param>
        /// <param name="orderClause">The results order-by clause</param>
        /// <returns>Returns a SELECT SQL command string</returns>
        public static string BuildSelect(string tableName, string[] fieldNames, string whereClause, string orderClause)
        {
            return BuildSqlCommand(SqlStringCommandType.Select, tableName, string.Format("{0} {1}", whereClause, orderClause), fieldNames, null);
        }
        
        /// <summary>
        /// Shorthand command to build a SQL string that will UPDATE values in a table
        /// </summary>
        /// <param name="tableName">The table to update values in</param>
        /// <param name="whereClause">SQL where-clause filter</param>
        /// <param name="fieldNames">The field names to update</param>
        /// <param name="fieldValues">The field values to update</param>
        /// <returns>Returns an UPDATE SQL command string</returns>
        public static string BuildUpdate(string tableName, string whereClause, string[] fieldNames, object[] fieldValues)
        {
            return BuildSqlCommand(SqlStringCommandType.Update, tableName, whereClause, fieldNames, fieldValues);
        }
        
        /// <summary>
        /// Shorthand command to build a SQL string that will DELETE records in a table
        /// </summary>
        /// <param name="tableName">The table to delete records from</param>
        /// <param name="whereClause">SQL where-clause filter</param>
        /// <returns>Returns a DELETE SQL command string</returns>
        public static string BuildDelete(string tableName, string whereClause)
        {
            return BuildSqlCommand(SqlStringCommandType.Delete, tableName, whereClause, null, null);
        }
        
        /// <summary>
        /// Shorthand command to build a SQL string that will EXECUTE a stored procedure
        /// </summary>
        /// <param name="commandName">The stored procedure to execute</param>
        /// <param name="parameterValues">optional parameters to pass to the stored procedure</param>
        /// <returns>Returns an EXECUTE SQL command string</returns>
        public static string BuildExecute(string commandName, params object[] parameterValues)
        {
            return BuildSqlCommand(SqlStringCommandType.Execute, commandName, string.Empty, null, parameterValues);
        }

        /// <summary>
        /// Converts an array of parameter objects into a comma delimited string of values
        /// </summary>
        /// <param name="arrayValue">Array of object values to convert</param>
        /// <returns>A comma delimited string of array values</returns>
        public static string ArrayToSQLString(object[] arrayValue)
        {
            string resultValue = string.Empty;

            for (int i = 0; i <= arrayValue.Length - 1; i++)
            {
                if (arrayValue[i] != DBNull.Value && arrayValue[i] != null)
                {
                    resultValue += Convert.ToString(arrayValue[i]);
                }

                if (i < arrayValue.Length - 1)
                {
                    resultValue += ",";
                }
            }

            return resultValue;
        }

        /// <summary>
        /// Converts an ArrayList of values into a comma delimited string of values
        /// </summary>
        /// <param name="arrayValue">ArrayList of values to convert</param>
        /// <returns>A comma delimited string of ArrayList values</returns>
        public static string ArrayToSQLString(ArrayList arrayValue)
        {
            return ArrayToSQLString(arrayValue.ToArray());
        }

        /// <summary>
        ///  Converts an array of object values into a comma delimited string as well
        ///  as converting null parameter values to "NULL" and single-quote parsing
        ///  string values.
        /// </summary>
        /// <param name="arrayValue">The array of values to convert</param>
        /// <returns>A comma delimited string of values</returns>
        public static string ArrayToSQLValues(object[] arrayValue)
        {
            if (arrayValue != null ? arrayValue.Length > 0 : false)
            {
                for (int i = 0; i < arrayValue.Length; i++)
                {
                    if (!Types.IsNullorDBNull(arrayValue[i]))
                    {
                        if (Types.IsBoolean(arrayValue[i].ToString()))
                        {
                            if (Types.FixBoolean(arrayValue[i]))
                            {
                                arrayValue[i] = 1;
                            }
                            else
                            {
                                arrayValue[i] = 0;
                            }
                        }
                        else if (!Types.IsNumeric(arrayValue[i]))
                        {
                            arrayValue[i] = string.Format("'{0}'", EncodeSingleQuotes(arrayValue[i].ToString()));
                        }
                    }
                    else
                    {
                        arrayValue[i] = "NULL";
                    }
                }
            }

            return ArrayToSQLString(arrayValue);
        }
        
        /// <summary>
        ///  Converts an ArrayList of values into a comma delimited string as well
        ///  as converting null parameter values to "NULL" and single-quote parsing
        ///  string values.
        /// </summary>
        /// <param name="arrayValue">The ArrayList of values to convert</param>
        /// <returns>A comma delimited string of values</returns>
        public static string ArrayToSQLValues(ArrayList arrayValue)
        {
            return ArrayToSQLValues(arrayValue.ToArray());
        }

        /// <summary>
        ///  Converts an array of object values into a comma delimited string including
        ///  a field name prefixed to each value.
        /// </summary>
        /// <param name="fieldNames">Field names to insert into the comma delimited string</param>
        /// <param name="fieldValues">Array of object values to convert</param>
        /// <returns>A comma delimited string of field=value pairs</returns>
        public static string ArrayToUpdateSQLValues(string[] fieldNames, object[] fieldValues)
        {
            string retValue = string.Empty;
            if (fieldNames != null ? fieldNames.Length > 0 : false)
            {
                if (fieldValues != null ? fieldValues.Length > 0 : false)
                {
                    if (fieldValues.Length == fieldValues.Length)
                    {
                        for (int i = 0; i < fieldValues.Length; i++)
                        {
                            retValue += fieldNames[i] + "=";

                            if (!Types.IsNullorDBNull(fieldValues[i]))
                            {
                                if (Types.IsBoolean(fieldValues[i].ToString()))
                                {
                                    if (Types.FixBoolean(fieldValues[i]))
                                    {
                                        fieldValues[i] = 1;
                                    }
                                    else
                                    {
                                        fieldValues[i] = 0;
                                    }
                                }
                                else if (!Types.IsNumeric(fieldValues[i]))
                                {
                                    fieldValues[i] = string.Format("'{0}'", EncodeSingleQuotes(fieldValues[i].ToString()));
                                }
                            }
                            else
                            {
                                fieldValues[i] = "NULL";
                            }

                            retValue += fieldValues[i].ToString();

                            if (i < fieldNames.Length)
                            {
                                retValue += ",";
                            }
                        }
                    }
                }
            }

            return retValue;
        }
        
        /// <summary>
        ///  Converts an ArrayList of values into a comma delimited string including
        ///  a field name prefixed to each value.
        /// </summary>
        /// <param name="fieldNames">Field names to insert into the comma delimited string</param>
        /// <param name="fieldValues">ArrayList of values to convert</param>
        /// <returns>A comma delimited string of field=value pairs</returns>
        public static string ArrayToUpdateSQLValues(string[] fieldNames, ArrayList fieldValues)
        {
            return ArrayToUpdateSQLValues(fieldNames, fieldValues.ToArray());
        }
        #endregion
    }
}
